Imports System.Data.SqlClient
Imports AppsUtility.EncDec

Public Class frmARageingReport

    Inherits System.Windows.Forms.Form
    Dim util As New AppsUtility.EncDec
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents cmdRefresh As DevComponents.DotNetBar.ButtonX
    Friend WithEvents cmdPrint As DevComponents.DotNetBar.ButtonX
    Friend WithEvents cmdExit As DevComponents.DotNetBar.ButtonX
    Friend WithEvents cmbCustGroup As DevComponents.DotNetBar.Controls.ComboBoxEx
    Friend WithEvents cmbCompany As DevComponents.DotNetBar.Controls.ComboBoxEx
    Friend WithEvents GroupPanel1 As DevComponents.DotNetBar.Controls.GroupPanel
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLString"))

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents dgArLedger As System.Windows.Forms.DataGrid
    Friend WithEvents dtpAge As System.Windows.Forms.DateTimePicker
    Friend WithEvents aRageingRpt1 As ARageingRpt
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Label1 = New System.Windows.Forms.Label()
        Me.dtpAge = New System.Windows.Forms.DateTimePicker()
        Me.dgArLedger = New System.Windows.Forms.DataGrid()
        Me.aRageingRpt1 = New ARageingRpt()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.Label3 = New System.Windows.Forms.Label()
        Me.cmdRefresh = New DevComponents.DotNetBar.ButtonX()
        Me.cmdPrint = New DevComponents.DotNetBar.ButtonX()
        Me.cmdExit = New DevComponents.DotNetBar.ButtonX()
        Me.cmbCustGroup = New DevComponents.DotNetBar.Controls.ComboBoxEx()
        Me.cmbCompany = New DevComponents.DotNetBar.Controls.ComboBoxEx()
        Me.GroupPanel1 = New DevComponents.DotNetBar.Controls.GroupPanel()
        CType(Me.dgArLedger, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.GroupPanel1.SuspendLayout()
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.AutoSize = True
        Me.Label1.BackColor = System.Drawing.Color.Transparent
        Me.Label1.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.Label1.Location = New System.Drawing.Point(13, 22)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(83, 18)
        Me.Label1.TabIndex = 47
        Me.Label1.Text = "Ageing Date"
        '
        'dtpAge
        '
        Me.dtpAge.Format = System.Windows.Forms.DateTimePickerFormat.[Short]
        Me.dtpAge.Location = New System.Drawing.Point(14, 67)
        Me.dtpAge.Name = "dtpAge"
        Me.dtpAge.Size = New System.Drawing.Size(108, 26)
        Me.dtpAge.TabIndex = 154
        '
        'dgArLedger
        '
        Me.dgArLedger.BackgroundColor = System.Drawing.Color.AliceBlue
        Me.dgArLedger.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.dgArLedger.CaptionVisible = False
        Me.dgArLedger.DataMember = ""
        Me.dgArLedger.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.dgArLedger.Location = New System.Drawing.Point(14, 101)
        Me.dgArLedger.Name = "dgArLedger"
        Me.dgArLedger.PreferredColumnWidth = 175
        Me.dgArLedger.ReadOnly = True
        Me.dgArLedger.Size = New System.Drawing.Size(983, 314)
        Me.dgArLedger.TabIndex = 249
        Me.dgArLedger.TabStop = False
        '
        'Label2
        '
        Me.Label2.AutoSize = True
        Me.Label2.BackColor = System.Drawing.Color.Transparent
        Me.Label2.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.Label2.Location = New System.Drawing.Point(311, 21)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(66, 18)
        Me.Label2.TabIndex = 47
        Me.Label2.Text = "Company"
        '
        'Label3
        '
        Me.Label3.AutoSize = True
        Me.Label3.BackColor = System.Drawing.Color.Transparent
        Me.Label3.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.Label3.Location = New System.Drawing.Point(125, 21)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(109, 18)
        Me.Label3.TabIndex = 47
        Me.Label3.Text = "Customer Group"
        '
        'cmdRefresh
        '
        Me.cmdRefresh.AccessibleRole = System.Windows.Forms.AccessibleRole.PushButton
        Me.cmdRefresh.Font = New System.Drawing.Font("Calibri", 11.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cmdRefresh.ForeColor = System.Drawing.Color.FromArgb(CType(CType(0, Byte), Integer), CType(CType(0, Byte), Integer), CType(CType(64, Byte), Integer))
        Me.cmdRefresh.Location = New System.Drawing.Point(827, 438)
        Me.cmdRefresh.Name = "cmdRefresh"
        Me.cmdRefresh.Size = New System.Drawing.Size(82, 27)
        Me.cmdRefresh.TabIndex = 252
        Me.cmdRefresh.Text = "Refresh"
        '
        'cmdPrint
        '
        Me.cmdPrint.AccessibleRole = System.Windows.Forms.AccessibleRole.PushButton
        Me.cmdPrint.Font = New System.Drawing.Font("Calibri", 11.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cmdPrint.ForeColor = System.Drawing.Color.FromArgb(CType(CType(0, Byte), Integer), CType(CType(0, Byte), Integer), CType(CType(64, Byte), Integer))
        Me.cmdPrint.Location = New System.Drawing.Point(739, 438)
        Me.cmdPrint.Name = "cmdPrint"
        Me.cmdPrint.Size = New System.Drawing.Size(82, 27)
        Me.cmdPrint.TabIndex = 251
        Me.cmdPrint.Text = "Print"
        '
        'cmdExit
        '
        Me.cmdExit.AccessibleRole = System.Windows.Forms.AccessibleRole.PushButton
        Me.cmdExit.Font = New System.Drawing.Font("Calibri", 11.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cmdExit.ForeColor = System.Drawing.Color.FromArgb(CType(CType(0, Byte), Integer), CType(CType(0, Byte), Integer), CType(CType(64, Byte), Integer))
        Me.cmdExit.Location = New System.Drawing.Point(915, 438)
        Me.cmdExit.Name = "cmdExit"
        Me.cmdExit.Size = New System.Drawing.Size(82, 27)
        Me.cmdExit.TabIndex = 250
        Me.cmdExit.Text = "Exit"
        '
        'cmbCustGroup
        '
        Me.cmbCustGroup.DisplayMember = "Text"
        Me.cmbCustGroup.DrawMode = System.Windows.Forms.DrawMode.OwnerDrawFixed
        Me.cmbCustGroup.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
        Me.cmbCustGroup.ItemHeight = 19
        Me.cmbCustGroup.Location = New System.Drawing.Point(128, 66)
        Me.cmbCustGroup.Name = "cmbCustGroup"
        Me.cmbCustGroup.Size = New System.Drawing.Size(174, 25)
        Me.cmbCustGroup.TabIndex = 253
        '
        'cmbCompany
        '
        Me.cmbCompany.DisplayMember = "Text"
        Me.cmbCompany.DrawMode = System.Windows.Forms.DrawMode.OwnerDrawFixed
        Me.cmbCompany.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
        Me.cmbCompany.ItemHeight = 19
        Me.cmbCompany.Location = New System.Drawing.Point(308, 66)
        Me.cmbCompany.Name = "cmbCompany"
        Me.cmbCompany.Size = New System.Drawing.Size(208, 25)
        Me.cmbCompany.TabIndex = 253
        '
        'GroupPanel1
        '
        Me.GroupPanel1.CanvasColor = System.Drawing.SystemColors.Control
        Me.GroupPanel1.ColorSchemeStyle = DevComponents.DotNetBar.eDotNetBarStyle.Office2007
        Me.GroupPanel1.Controls.Add(Me.Label2)
        Me.GroupPanel1.Controls.Add(Me.Label1)
        Me.GroupPanel1.Controls.Add(Me.Label3)
        Me.GroupPanel1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.GroupPanel1.Location = New System.Drawing.Point(0, 0)
        Me.GroupPanel1.Name = "GroupPanel1"
        Me.GroupPanel1.Size = New System.Drawing.Size(1011, 474)
        '
        '
        '
        Me.GroupPanel1.Style.BackColor2SchemePart = DevComponents.DotNetBar.eColorSchemePart.PanelBackground2
        Me.GroupPanel1.Style.BackColorGradientAngle = 90
        Me.GroupPanel1.Style.BackColorSchemePart = DevComponents.DotNetBar.eColorSchemePart.PanelBackground
        Me.GroupPanel1.Style.BorderBottom = DevComponents.DotNetBar.eStyleBorderType.Solid
        Me.GroupPanel1.Style.BorderBottomWidth = 1
        Me.GroupPanel1.Style.BorderColorSchemePart = DevComponents.DotNetBar.eColorSchemePart.PanelBorder
        Me.GroupPanel1.Style.BorderLeft = DevComponents.DotNetBar.eStyleBorderType.Solid
        Me.GroupPanel1.Style.BorderLeftWidth = 1
        Me.GroupPanel1.Style.BorderRight = DevComponents.DotNetBar.eStyleBorderType.Solid
        Me.GroupPanel1.Style.BorderRightWidth = 1
        Me.GroupPanel1.Style.BorderTop = DevComponents.DotNetBar.eStyleBorderType.Solid
        Me.GroupPanel1.Style.BorderTopWidth = 1
        Me.GroupPanel1.Style.CornerDiameter = 4
        Me.GroupPanel1.Style.CornerType = DevComponents.DotNetBar.eCornerType.Rounded
        Me.GroupPanel1.Style.TextAlignment = DevComponents.DotNetBar.eStyleTextAlignment.Center
        Me.GroupPanel1.Style.TextColorSchemePart = DevComponents.DotNetBar.eColorSchemePart.PanelText
        Me.GroupPanel1.Style.TextLineAlignment = DevComponents.DotNetBar.eStyleTextAlignment.Near
        Me.GroupPanel1.TabIndex = 254
        Me.GroupPanel1.Text = "AR - Ageing Report"
        '
        'ARageingReport
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(7, 19)
        Me.BackColor = System.Drawing.SystemColors.ActiveCaption
        Me.ClientSize = New System.Drawing.Size(1011, 474)
        Me.ControlBox = False
        Me.Controls.Add(Me.cmbCompany)
        Me.Controls.Add(Me.cmbCustGroup)
        Me.Controls.Add(Me.cmdRefresh)
        Me.Controls.Add(Me.cmdPrint)
        Me.Controls.Add(Me.cmdExit)
        Me.Controls.Add(Me.dtpAge)
        Me.Controls.Add(Me.dgArLedger)
        Me.Controls.Add(Me.GroupPanel1)
        Me.Font = New System.Drawing.Font("Calibri", 11.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.ForeColor = System.Drawing.SystemColors.ControlLightLight
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.Location = New System.Drawing.Point(50, 50)
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "ARageingReport"
        Me.ShowInTaskbar = False
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        CType(Me.dgArLedger, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupPanel1.ResumeLayout(False)
        Me.GroupPanel1.PerformLayout()
        Me.ResumeLayout(False)

    End Sub

#End Region
    Dim sqlCon As SqlConnection = New SqlConnection(strCon)
    Dim strsql As String
    Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
    Dim rdr As SqlDataReader
    Dim OldTodate, OldFrdate As String
    Dim Bqty As Integer
    Dim FrDt, ToDt As String
    Private Sub Budgets_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ARdata As New ARdataLogic
        Dim ds As DataSet
        ds = ARdata.GetAllCompany()
        cmbCompany.DataSource = ds.Tables(0)
        cmbCompany.ValueMember = "CompanyID"
        cmbCompany.DisplayMember = "CompanyName"
        cmbCompany.SelectedValue = CompanyID
        LoadGroupCombos(CompanyID)
        '
    End Sub
    Private Sub LoadGroupCombos(ByVal CompanyCode As Integer)
        Dim ARdata As New ARdataLogic
        Dim ds As DataSet

        ds = ARdata.GetCustomerGroups()
        cmbCustGroup.DataSource = ds.Tables(0)
        cmbCustGroup.ValueMember = "GroupID"
        cmbCustGroup.DisplayMember = "GroupName"
        cmbCustGroup.SelectedValue = 0
        '
        ARdata = Nothing
    End Sub
    Private Sub dgArLedger_load()
        Try
            Dim FrDt, ToDt, within30last, over30last, over30first As String

            FrDt = dtpAge.Text
            FrDt = FrDt.Substring(6, 4) & "-" & FrDt.Substring(3, 2) & "-" & FrDt.Substring(0, 2)
            Dim WhichCompany As String = " "
            If cmbCompany.SelectedValue > 1 Then
                WhichCompany = " and x.CompanyID=" & cmbCompany.SelectedValue & " and b.cusid=x.cusid "
            ElseIf cmbCompany.SelectedValue = 1 Then
                WhichCompany = " and b.cusid=x.cusid "
            Else
                MsgBox("Select company", MsgBoxStyle.Information, "Inventory Manager")
                cmbCompany.Focus()
                Exit Sub
            End If


            strsql = "select * from  " & _
"(select a.CusCode,a.CusName,sum(balance) Balance, " & _
"sum(C_within30) C_within30,sum(D_within30) D_within30,sum(C_f31to60) C_f31to60,sum(D_f31to60) D_f31to60, " & _
"sum(C_f61to90) C_f61to90,sum(D_f61to90) D_f61to90,sum(C_f91to120) C_f91to120,sum(D_f91to120) D_f91to120, " & _
"sum(C_f121nover) C_f121nover,sum(D_f121nover) D_f121nover,Sum(opbal) opbal " & _
"from Customer a, " & _
"( " & _
"select b.cusid,sum(docamt) C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120, " & _
"0 D_f91to120,0 C_f121nover,0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') <= 30) " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & " and (DocType = 'D' or DocType = 'R' or DocType = 'C') and DocNum <> 'BBF' " & _
"group by b.cusid  " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,sum(docamt) D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120, " & _
"0 D_f91to120,0 C_f121nover,0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') <= 30) " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & " and DocType = 'S' and DocNum <> 'BBF' " & _
"group by b.cusid  " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,sum(docamt) C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120, " & _
"0 D_f91to120,0 C_f121nover,0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') > 30) " & _
"and (DateDiff(Day, docdate, '" & FrDt & "') <= 60) and (DocType = 'D' or DocType = 'R' or DocType = 'C') and DocNum <> 'BBF' " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & _
"group by b.cusid  " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,sum(docamt) D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120, " & _
"0 D_f91to120,0 C_f121nover,0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') > 30)  " & _
"and (DateDiff(Day, docdate, '" & FrDt & "') <= 60) and DocType = 'S' and DocNum <> 'BBF' " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & _
"group by b.cusid  " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,sum(docamt) C_f61to90,0 D_f61to90,0 C_f91to120, " & _
"0 D_f91to120,0 C_f121nover,0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') > 60)  " & _
"and (DateDiff(Day, docdate, '" & FrDt & "') <= 90)  and (DocType = 'D' or DocType = 'R' or DocType = 'C') and DocNum <> 'BBF' " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & _
"group by b.cusid " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,sum(docamt) D_f61to90,0 C_f91to120, " & _
"0 D_f91to120,0 C_f121nover,0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') > 60)  " & _
"and (DateDiff(Day, docdate, '" & FrDt & "') <= 90)  and DocType = 'S' and DocNum <> 'BBF' " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & _
"group by b.cusid  " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,sum(docamt) C_f91to120,0 D_f91to120,0 C_f121nover " & _
",0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') > 90)  " & _
"and (DateDiff(Day, docdate, '" & FrDt & "') <= 120)  and (DocType = 'D' or DocType = 'R' or DocType = 'C') and DocNum <> 'BBF' " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & _
"group by b.cusid   " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120,sum(docamt) D_f91to120,0 C_f121nover " & _
",0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') > 90) " & _
"and (DateDiff(Day, docdate, '" & FrDt & "') <= 120) and docdate<='" & FrDt & "'  " & WhichCompany & _
"and  DocType = 'S' and DocNum <> 'BBF' " & _
"group by b.cusid  " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120,0 D_f91to120,sum(docamt) C_f121nover " & _
",0 D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') > 120)  " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & _
"and (DocType = 'D' or DocType = 'R' or DocType = 'C') and DocNum <> 'BBF' " & _
"group by b.cusid " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120,0 D_f91to120,0 C_f121nover " & _
",sum(docamt) D_f121nover,0 balance,0 opbal " & _
"from artrx b,CompanyCustomer x where(DateDiff(Day, docdate, '" & FrDt & "') > 120)  " & _
"and docdate<='" & FrDt & "'  " & WhichCompany & _
"and  DocType = 'S' and DocNum <> 'BBF' " & _
"group by b.cusid  " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120,0 D_f91to120, " & _
"0 C_f121nover,0 D_f121nover,sum(docamt) balance,0 opbal " & _
"from artrx b,CompanyCustomer x where docdate<='" & FrDt & "' " & WhichCompany & _
"group by b.cusid  " & _
"UNION ALL " & _
"select b.cusid,0 C_within30,0 D_within30,0 C_f31to60,0 D_f31to60,0 C_f61to90,0 D_f61to90,0 C_f91to120,0 D_f91to120, " & _
"0 C_f121nover,0 D_f121nover,0 balance,sum(docamt) opbal " & _
"from artrx b,CompanyCustomer x where docdate<='" & FrDt & "' " & WhichCompany & " and DocNum = 'BBF' " & _
"group by b.cusid " & _
") b    " & _
"where(a.CusID = b.CusID) " & _
"group by a.CusCode,a.CusName) c   " & _
"where(Balance <> 0 Or C_within30 <> 0 Or D_within30 <> 0 Or C_f31to60 <> 0 Or D_f31to60 <> 0 Or C_f61to90 <> 0 Or D_f61to90 <> 0) " & _
"or C_f91to120<>0 or D_f91to120<>0 or C_f121nover<>0 or D_f121nover<>0   " & _
"order by CusName "

            'Try

            Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
            Dim ds As New DataSet("TRENQ")
            da.Fill(ds, "TRENQ")

            Dim Bal, Day30, Day60, Day90, Over90 As Double
            Dim drow As DataRow

            ds.Tables("TRENQ").Columns.Add("Balanced")
            ds.Tables("TRENQ").Columns.Add("within30")
            ds.Tables("TRENQ").Columns.Add("over30")
            ds.Tables("TRENQ").Columns.Add("over60")
            ds.Tables("TRENQ").Columns.Add("over90")
            ds.Tables("TRENQ").Columns.Add("over120")


            For Each drow In ds.Tables("TRENQ").Rows

                drow("over120") = (CDbl(drow("opbal")) + CDbl(drow("D_f121nover")) + CDbl(drow("C_f121nover"))).ToString("#0.000")
                ''Block 1 : over120
                If drow("over120") > 0 Then
                    drow("C_f121nover") = 0
                    drow("over120") = (CDbl(drow("over120")) + CDbl(drow("C_f91to120"))).ToString("#0.000")
                    If drow("over120") > 0 Then
                        drow("over120") = (CDbl(drow("over120")) + CDbl(drow("C_f61to90"))).ToString("#0.000")
                        drow("C_f91to120") = 0
                        If drow("over120") > 0 Then
                            drow("C_f61to90") = 0
                            drow("over120") = (CDbl(drow("over120")) + CDbl(drow("C_f31to60"))).ToString("#0.000")
                            If drow("over120") > 0 Then
                                drow("C_f31to60") = 0
                                drow("over120") = (CDbl(drow("over120")) + CDbl(drow("C_within30"))).ToString("#0.000")
                                If drow("over120") > 0 Then
                                    drow("C_within30") = 0
                                Else
                                    drow("C_within30") = drow("over120")
                                    drow("over120") = 0
                                End If
                            Else
                                drow("C_f31to60") = drow("over120")
                                drow("over120") = 0
                            End If
                        Else
                            drow("C_f61to90") = drow("over120")
                            drow("over120") = 0
                        End If
                    Else
                        drow("C_f91to120") = drow("over120")
                        drow("over120") = 0
                    End If
                Else
                    drow("C_f121nover") = drow("over120")
                    drow("over120") = 0
                End If

                ''Block 2 : over90
            Next


            For Each drow In ds.Tables("TRENQ").Rows
                drow("over90") = (CDbl(drow("D_f91to120")) + CDbl(drow("C_f91to120"))).ToString("#0.000")
                If drow("over90") > 0 Then
                    drow("C_f91to120") = 0
                    drow("over90") = (CDbl(drow("over90")) + CDbl(drow("C_f61to90"))).ToString("#0.000")
                    If drow("over90") > 0 Then
                        drow("C_f61to90") = 0
                        drow("over90") = (CDbl(drow("over90")) + CDbl(drow("C_f31to60"))).ToString("#0.000")
                        If drow("over90") > 0 Then
                            drow("C_f31to60") = 0
                            drow("over90") = (CDbl(drow("over90")) + CDbl(drow("C_within30"))).ToString("#0.000")
                            If drow("over90") > 0 Then
                                drow("C_within30") = 0
                            Else
                                drow("C_within30") = drow("over90")
                                drow("over90") = 0
                            End If
                        Else
                            drow("C_f31to60") = drow("over90")
                            drow("over90") = 0
                        End If
                    Else
                        drow("C_f61to90") = drow("over90")
                        drow("over90") = 0
                    End If
                Else
                    drow("C_f91to120") = drow("over90")
                    drow("over90") = 0
                End If
            Next

            For Each drow In ds.Tables("TRENQ").Rows
                ''Block 3 : over60
                drow("over60") = (CDbl(drow("D_f61to90")) + CDbl(drow("C_f61to90"))).ToString("#0.000")
                If drow("over60") > 0 Then
                    drow("C_f61to90") = 0
                    drow("over60") = (CDbl(drow("over60")) + CDbl(drow("C_f31to60"))).ToString("#0.000")
                    If drow("over60") > 0 Then
                        drow("C_f31to60") = 0
                        drow("over60") = (CDbl(drow("over60")) + CDbl(drow("C_within30"))).ToString("#0.000")
                        If drow("over60") > 0 Then
                            drow("C_within30") = 0
                        Else
                            drow("C_within30") = drow("over60")
                            drow("over60") = 0
                        End If
                    Else
                        drow("C_f31to60") = drow("over60")
                        drow("over60") = 0
                    End If
                Else
                    drow("C_f61to90") = drow("over60")
                    drow("over60") = 0
                End If

            Next


            For Each drow In ds.Tables("TRENQ").Rows
                ''Block 4 : over30
                drow("over30") = (CDbl(drow("D_f31to60")) + CDbl(drow("C_f31to60"))).ToString("#0.000")
                If drow("over30") > 0 Then
                    drow("C_f31to60") = 0
                    drow("over30") = (CDbl(drow("over30")) + CDbl(drow("C_within30"))).ToString("#0.000")
                    If drow("over30") > 0 Then
                        drow("C_within30") = 0
                    Else
                        drow("C_within30") = drow("over30")
                        drow("over30") = 0
                    End If
                Else
                    drow("C_f31to60") = drow("over30")
                    drow("over30") = 0
                End If
            Next

            For Each drow In ds.Tables("TRENQ").Rows
                ''Block 5 : within30
                drow("within30") = CDbl(drow("D_within30")) + CDbl(drow("C_within30")) + CDbl(drow("C_f31to60")) _
                + CDbl(drow("C_f61to90")) + CDbl(drow("C_f91to120")) + CDbl(drow("C_f121nover"))
            Next

            Dim b, c, o30, o60, o90, o120 As Decimal
            b = 0.0
            c = 0.0
            o30 = 0.0
            o60 = 0.0
            o90 = 0.0
            o120 = 0.0

            For Each drow In ds.Tables("TRENQ").Rows
                If drow("Balance").ToString() = "0.000" Then
                    drow.Delete()
                Else
                    b = b + CDbl(drow("Balance"))
                    c = c + CDbl(drow("within30"))
                    o30 = o30 + CDbl(drow("over30"))
                    o60 = o60 + CDbl(drow("over60"))
                    o90 = o90 + CDbl(drow("over90"))
                    o120 = o120 + CDbl(drow("over120"))

                    If drow("within30") < 0 Then
                        drow("within30") = 0 - drow("within30")
                        drow("within30") = "( " & drow("within30") & " )"
                    End If
                    If drow("over30") < 0 Then
                        drow("over30") = 0 - drow("over30")
                        drow("over30") = "( " & drow("over30") & " )"
                    End If
                    If drow("over60") < 0 Then
                        drow("over60") = 0 - drow("over60")
                        drow("over60") = "( " & drow("over60") & " )"
                    End If
                    If drow("over90") < 0 Then
                        drow("over90") = 0 - drow("over90")
                        drow("over90") = "( " & drow("over90") & " )"
                    End If
                    If drow("over120") < 0 Then
                        drow("over120") = 0 - drow("over120")
                        drow("over120") = "( " & drow("over120") & " )"
                    End If
                    If drow("Balance") < 0 Then
                        drow("Balanced") = 0 - drow("Balance")
                        drow("Balanced") = "( " & drow("Balanced") & " )"
                    Else
                        drow("Balanced") = drow("Balance")
                    End If
                End If
            Next

            drow = ds.Tables("TRENQ").NewRow()
            drow("CusName") = "TOTAL:"
            drow("Balanced") = b.ToString("#0.000")
            drow("Within30") = c.ToString("#0.000")
            drow("Over30") = o30.ToString("#0.000")
            drow("Over60") = o60.ToString("#0.000")
            drow("Over90") = o90.ToString("#0.000")
            drow("over120") = o120.ToString("#0.000")
            ds.Tables("TRENQ").Rows.Add(drow)

            dgArLedger.DataSource = ds
            dgArLedger.DataMember = "TRENQ"
            dgArLedger.ReadOnly = True
            dgArLedger.TableStyles.Clear()
            dgArLedger_Style()
            '
            sqlCon.Close()
            '
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub dgArLedger_Style()
        With dgArLedger
            .TableStyles.Clear()
            .CaptionBackColor = System.Drawing.Color.SlateBlue
            .CaptionForeColor = System.Drawing.Color.LemonChiffon
            .AllowSorting = True
            .AlternatingBackColor = System.Drawing.Color.AliceBlue
            .ParentRowsBackColor = System.Drawing.Color.Lavender
            .ParentRowsForeColor = System.Drawing.Color.SlateBlue
            .HeaderBackColor = System.Drawing.Color.LemonChiffon
            .AllowNavigation = False
            .ParentRowsVisible = False
            .RowHeadersVisible = False
        End With

        Dim grdTableStyle As New DataGridTableStyle
        With grdTableStyle
            .AlternatingBackColor = Color.AliceBlue
            .HeaderFont = New Font("Calibri", 9.0!, FontStyle.Bold)
            .MappingName = "TRENQ"
            .PreferredColumnWidth = 160
            .HeaderBackColor = Color.LightSlateGray
            .HeaderForeColor = Color.LightCyan
            .PreferredRowHeight = 15
            .RowHeadersVisible = False
            .RowHeaderWidth = 0
        End With

        Dim cs As DataGridColumnStyle
        cs = New DataGridTextBoxColumn
        cs.Width = 60
        cs.MappingName = "CusCode"
        cs.HeaderText = "Code"
        cs.NullText = ""
        grdTableStyle.GridColumnStyles.Add(cs)

        Dim cs1 As DataGridColumnStyle
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 275
        cs1.MappingName = "CusName"
        cs1.HeaderText = "Customer Name"
        cs1.NullText = ""
        grdTableStyle.GridColumnStyles.Add(cs1)

        Dim cs7 As DataGridColumnStyle
        cs7 = New DataGridTextBoxColumn  ' Add the standard column style.
        cs7.Width = 105
        cs7.MappingName = "Balanced"
        cs7.HeaderText = "Balance"
        cs7.NullText = ""
        cs7.ReadOnly = True
        cs7.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs7)

        Dim cs2 As DataGridColumnStyle
        cs2 = New DataGridTextBoxColumn  ' Add the standard column style.
        cs2.Width = 105
        cs2.MappingName = "within30"
        cs2.HeaderText = "Current"
        cs2.NullText = ""
        cs2.ReadOnly = True
        cs2.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs2)

        Dim cs3 As DataGridColumnStyle
        cs3 = New DataGridTextBoxColumn  ' Add the standard column style.
        cs3.Width = 105
        cs3.MappingName = "over30"
        cs3.HeaderText = "Over 30 Days"
        cs3.NullText = ""
        cs3.ReadOnly = True
        cs3.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs3)

        Dim cs4 As DataGridColumnStyle
        cs4 = New DataGridTextBoxColumn  ' Add the standard column style.
        cs4.Width = 105
        cs4.MappingName = "over60"
        cs4.HeaderText = "Over 60 Days"
        cs4.NullText = ""
        cs4.ReadOnly = True
        cs4.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs4)

        Dim cs5 As DataGridColumnStyle
        cs5 = New DataGridTextBoxColumn  ' Add the standard column style.
        cs5.Width = 105
        cs5.MappingName = "over90"
        cs5.HeaderText = "Over 90 Days"
        cs5.NullText = ""
        cs5.ReadOnly = True
        cs5.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs5)

        'Dim cs5 As DataGridColumnStyle
        cs5 = New DataGridTextBoxColumn  ' Add the standard column style.
        cs5.Width = 105
        cs5.MappingName = "over120"
        cs5.HeaderText = "Over 120 Days"
        cs5.NullText = ""
        cs5.ReadOnly = True
        cs5.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs5)

        dgArLedger.TableStyles.Add(grdTableStyle)

    End Sub

    Private Sub PrintArAge()
        Dim ds As DataSet = New DataSet("SerpsRpt")
        Dim table As DataTable = New DataTable("SerpsRpt")
        table.Columns.Add("Str1")
        table.Columns.Add("Dec1")
        table.Columns.Add("Dec2")
        table.Columns.Add("Dec3")
        table.Columns.Add("Dec4")
        table.Columns.Add("Dec5")
        table.Columns.Add("Dec6")
        table.Columns.Add("Dec7")
        Dim mrow, drow As Integer
        Dim cm As CurrencyManager = BindingContext(dgArLedger.DataSource, dgArLedger.DataMember)
        Dim dv As DataView = cm.List
        dv.RowStateFilter = DataViewRowState.CurrentRows
        mrow = dv.Count
        Dim n As Integer
        For n = 0 To mrow - 1
            Dim nrow As DataRow = table.NewRow()
            nrow("Str1") = dgArLedger.Item(n, 0)
            nrow("Dec1") = dgArLedger.Item(n, 1)
            nrow("Dec2") = dgArLedger.Item(n, 2)
            nrow("Dec3") = dgArLedger.Item(n, 3)
            nrow("Dec4") = dgArLedger.Item(n, 4)
            nrow("Dec5") = dgArLedger.Item(n, 5)
            nrow("Dec6") = dgArLedger.Item(n, 6)
            nrow("Dec7") = dgArLedger.Item(n, 7)
            table.Rows.Add(nrow)
        Next
        ds.Tables.Add(table)

        aRageingRpt1.Load()
        aRageingRpt1.SetDataSource(ds)

        aRageingRpt1.SetParameterValue("CompanyName", CompanyTitle)
        aRageingRpt1.SetParameterValue("ReportTitle", "A/R Ageing Report")
        aRageingRpt1.SetParameterValue("AgeingDate", dtpAge.Text)
        aRageingRpt1.SetParameterValue("user", UserName)

        If PrntPrv Is Nothing Then
            PrntPrv = New PrintPreviewForm
            PrntPrv.CrRptView.ReportSource = aRageingRpt1
            PrntPrv.ShowDialog()
        End If
        aRageingRpt1.Close()
        'arageingrpt1.PrintOptions.PrinterName = InvoicePrinter
        'Try
        '    arageingrpt1.PrintToPrinter(1, True, 0, 0)
        'Catch ex As Exception
        '    MsgBox(ex.ToString)
        'End Try
    End Sub

    Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
        Me.Hide()
    End Sub

    Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
        Me.Cursor = Cursors.WaitCursor
        dgArLedger_load()
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
        Me.Cursor = Cursors.WaitCursor
        PrintArAge()
        Me.Cursor = Cursors.WaitCursor
    End Sub

End Class
